package toko.service.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.util.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;

import toko.domain.Buku;
import toko.domain.Penjualan;
import toko.domain.PenjualanDetail;
import toko.service.TokoService;

public class TokoServiceJDBC implements TokoService{
	private static final String sqlInsert="insert into buku (kode, judul, pengarang, penerbit, harga)" +
	"values (?, ?, ?, ?, ?)";
	private static final String sqlselectById = "select * from buku where id=? ";
	private static final String sqlselectByKode = "select * from buku where kode like ? ";
	private static final String sqlselectByJudul = "select * from buku where judul like ? ";
	private static final String sqlDeleteById = "delete from buku where id=? ";
	private static final String sqlMaxId = "select last_insert_id()";
	private static final String sqlInsertPenjualan = "insert into penjualan(waktu, kasir, pembayaran)"+
	"values (?,?,?)";
	private static final String sqlInsertPDetail = "insert into penjualandetail(buku,qty,PenjualanID)"+
	"values (?,?,?)";
	private static final String sqlCariPenjualan = "select * from Penjualan where date(waktu) between date(?) and date(?)";
	private static final String sqlJumlahBukuTerjual = 
		"select sum(qty) from penjualandetail pd inner join buku on pd.buku = buku.id " +
		"inner join penjualan on pd.penjualanid = penjualan.id " +
		"where buku.id = ? and date(waktu) between date(?) and date(?) group by buku.id";
	
	private Connection connection;
	private PreparedStatement psInsert;
	private PreparedStatement psSelectById;
	private PreparedStatement psSelectByKode;
	private PreparedStatement psSelectByJudul;
	private PreparedStatement psDeleteById;
	private PreparedStatement psInsertPenjualan;
	private PreparedStatement psInsertPDetail;
	private PreparedStatement psSelectMaxId;
	private PreparedStatement psCariPenjualan;
	private PreparedStatement psJmlBukuTerjual;
	private TokoService tokoService;
	
	public TokoServiceJDBC(Connection connection) throws Exception{
		this.connection = connection;
		psInsert = connection.prepareStatement(sqlInsert);
		psSelectById = connection.prepareStatement(sqlselectById);
		psSelectByKode = connection.prepareStatement(sqlselectByKode);
		psSelectByJudul = connection.prepareStatement(sqlselectByJudul);
		psDeleteById = connection.prepareStatement(sqlDeleteById);
		psInsertPenjualan = connection.prepareStatement(sqlInsertPenjualan);
		psInsertPDetail = connection.prepareStatement(sqlInsertPDetail);
		psSelectMaxId = connection.prepareStatement(sqlMaxId);
		psCariPenjualan = connection.prepareStatement(sqlCariPenjualan);
		psJmlBukuTerjual = connection.prepareStatement(sqlJumlahBukuTerjual);
	}
	
	
	
	@Override
	public void simpan(Buku buku) {
		// TODO Auto-generated method stub
		//System.out.println(buku.getKode());
		try{
			psInsert.setString(1, buku.getKode());
			psInsert.setString(2, buku.getJudul());
			psInsert.setString(3, buku.getPengarang());
			psInsert.setString(4, buku.getPenerbit());
			psInsert.setBigDecimal(5, buku.getHarga());
			psInsert.executeUpdate();
		}catch (SQLException e){
			e.printStackTrace();
		}
	}

	@Override
	public void hapus(Buku buku) {
		try {
			psDeleteById.setInt(1, new Integer(100));
			psDeleteById.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public void importCSV(String filename) {
		Buku b = new Buku(); 
		try{
			connection.setAutoCommit(false);
			BufferedReader reader = new BufferedReader(new FileReader(filename));
			String data;
			while((data = reader.readLine())!=null){
				String[] result = data.split(";");
		     	b.setKode(result[0]);
		     	b.setJudul(result[1]);
		     	b.setPengarang(result[2]);
		     	b.setPenerbit(result[3]);
		     	b.setHarga(new BigDecimal(result[4]));
				simpan(b);
			}
			reader.close();
			connection.commit();	
		} catch (Exception e) {
			try {
				System.out.println("Rollback, terjadi error");
				connection.rollback();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

	
	 
	@Override
	public Buku cariId(Integer id) {
		
		try {
			psSelectById.setInt(1,id);
			ResultSet rs = psSelectById.executeQuery();
			if(!rs.next()) return null;
			
			Buku b = konversiResultSetMenjadiBuku(rs);
			return b;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	private Buku konversiResultSetMenjadiBuku(ResultSet rs)throws Exception{
		Buku b = new Buku();
		b.setId(rs.getInt("id"));
		b.setKode(rs.getString("kode"));
		b.setJudul(rs.getString("judul"));
		b.setPenerbit(rs.getString("penerbit"));
		b.setPengarang(rs.getString("pengarang"));
		b.setHarga(rs.getBigDecimal("harga"));
		return b;
	} 

	@Override
	public List<Buku> cariKode(String kode) {
		try {
			List<Buku> hasil = new ArrayList<Buku>();
			psSelectByKode.setString(1, "%"+kode+"%");
			ResultSet rs = psSelectByKode.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Buku> cariJudul(String judul) {
		try {
			List<Buku> hasil = new ArrayList<Buku>();
			psSelectByJudul.setString(1, "%"+judul+"%");
			ResultSet rs = psSelectByJudul.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}



	public void simpanPenjualan(Penjualan p) {
		try {
			connection.setAutoCommit(false);
			psInsertPenjualan.setDate(1, new java.sql.Date (p.getWaktuTransaksi().getTime()));
			psInsertPenjualan.setString(2, p.getKasir());
			psInsertPenjualan.setBigDecimal(3, p.getPembayaran());
			psInsertPenjualan.executeUpdate();
			ResultSet rs = psSelectMaxId.executeQuery();
			if(!rs.next())throw new IllegalStateException("Autogenerated ID tidak bisa diambil");
			Integer id = rs.getInt(1);
			
			for(PenjualanDetail d : p.getDaftarPenjualanDetail()){
				psInsertPDetail.setInt(3, id);
				psInsertPDetail.setInt(1, d.getBuku().getId());
				psInsertPDetail.setInt(2, d.getQuantity());
				psInsertPDetail.executeUpdate();
			}
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}



	@Override
	public List<Penjualan> cariPenjualan(Date mulai, Date sampai) {
		try {
			List<Penjualan> hasil = new ArrayList<Penjualan>();
			
			psCariPenjualan.setDate(1, new java.sql.Date (mulai.getTime()));
			psCariPenjualan.setDate(2, new java.sql.Date (sampai.getTime()));
			ResultSet rs = psCariPenjualan.executeQuery();
			while(rs.next()){
				Penjualan p = konversiResultSetMenjadiPenjualan(rs);
				hasil.add(p);
			}
			return hasil;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	private Penjualan konversiResultSetMenjadiPenjualan(ResultSet rs) throws Exception{
		Penjualan p = new Penjualan();
		p.setId(rs.getInt("id"));
		p.setKasir(rs.getString("kasir"));
		p.setWaktuTransaksi(rs.getDate("waktu"));
		p.setPembayaran(rs.getBigDecimal("pembayaran"));
		return p;
	}

	@Override
	public Integer jumlahBukuTerjual(Buku b, Date mulai, Date sampai) {
		Integer jml = 1;
		try {
			psJmlBukuTerjual.setInt(1, 105);
			psJmlBukuTerjual.setDate(2, new java.sql.Date (mulai.getTime()));
			psJmlBukuTerjual.setDate(3, new java.sql.Date (sampai.getTime()));
			ResultSet rs = psJmlBukuTerjual.executeQuery();
			if(rs.next()){
				jml = rs.getInt(1);
				
			}
			return jml;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
